# databases.py
"""This module is intended to perform actions on a database and demonstrate sqlite tools"""
import sqlite3, os, tempfile, shutil
import calendar

# Here's a useful function definition

def printTable(dbConn):
    """This function prints the rows and columns of the table"""
    is_first = True
    for row in dbConn:
        if is_first : 
            print(row.keys())
        is_first = False
        rowstr = ""
        for col in range(len(row)):
            rowstr += "       " 
            rowstr += str(row[col]).rstrip()
        rowstr += "       " 
        print rowstr


# First, we need to read the database into memory.
# If the database doesn't exist, report as much. Otherwise delete
# any copies currently in the temporary directory, make a new copy there,
# and connect to it.

db = "ciTests.db"

if not os.path.exists(db) :
    raise Exception, "Error: could not find specified database."

dbConn = sqlite3.connect('ciTests.db', detect_types=sqlite3.PARSE_COLNAMES)
dbConn.row_factory = sqlite3.Row

if type(dbConn) != sqlite3.Connection :
    raise Exception, "Error, not a valid database connection."

c = dbConn.cursor()

# <demo> stop
# Now that we're connected, we can issue commands
# The first of these is SELECT
# The syntax is SELECT <field> FROM <Table>
# (The asterisk * is "regular expression" meaning "everything")
c.execute("SELECT * FROM PersonTable")
printTable(c)

# <demo> stop
# The table name must match one of the table names in the database 
c.execute("SELECT * FROM ResultsTable")
printTable(c)

# <demo> stop
# We can just select a single field of interest

c.execute("SELECT personID FROM PersonTable")
printTable(c)

# <demo> stop
# We can also select numerous fields of interest
c.execute("SELECT personID, username FROM PersonTable")
printTable(c)

# <demo> stop
# We can select them out of order
c.execute("SELECT birthyear, personID, username FROM PersonTable")
printTable(c)

# <demo> stop
# We can even select some fields numerous times
c.execute("SELECT birthyear, personID, username, personID FROM PersonTable")
printTable(c)

# <demo> stop
# Sorting allows the whole table to be sorted by a single field
# The sql keyword for sorting is ORDER BY
c.execute("SELECT * FROM ResultsTable")
printTable(c)

# <demo> stop
# And it can be stated whether to sort in ASCending or DESCending order
# The default is ASCending order
c.execute("SELECT * FROM ResultsTable ORDER BY personID")
printTable(c)
c.execute("SELECT * FROM ResultsTable ORDER BY personID DESC")
printTable(c)

# <demo> stop
# Sorting also allows for sorting the data by one field and then by another

c.execute("SELECT * FROM ResultsTable ORDER BY personID DESC, month ASC")
printTable(c)

# <demo> stop
# It is even possible to sort by a field that was not selected
c.execute("SELECT birthyear, personID, username FROM PersonTable")
printTable(c)
c.execute("SELECT birthyear, personID FROM PersonTable ORDER BY username")
printTable(c)

# <demo> stop
# Another thing we can do is filter the results
# Filtering allows many operations to be simplified
# The SQL filtering keyword is WHERE
c.execute("SELECT * FROM ResultsTable WHERE personID = (?)",(1,))
printTable(c)

# <demo> stop

# Filtering commands are piped together with previous commands
c.execute("SELECT range FROM ResultsTable WHERE personID >= (?)",(1,))
printTable(c)

# <demo> stop
# The AND keyword allows you to filter on numerous fields
c.execute("SELECT * FROM ResultsTable WHERE personID >= (?) AND resultID> (?)",(1,2))
printTable(c)


# Aggregating can summarize many records into new ones.
# sums
c.execute("SELECT SUM(discrimination) FROM ResultsTable")
printTable(c)

# counts
c.execute("SELECT COUNT(personID) FROM ResultsTable")
printTable(c)

# <demo> stop
# Joining allows many tables to be joined
c.execute("SELECT * FROM ResultsTable JOIN PersonTable")
printTable(c)

# <demo> stop
# Here we only return these joined rows if they share a field
c.execute("SELECT * FROM ResultsTable JOIN PersonTable ON \
        PersonTable.PersonID = ResultsTable.PersonID")
printTable(c)

# <demo> stop






